// db.js
const mysql = require('mysql2/promise');
const dotenv = require('dotenv');
dotenv.config();

// 数据库连接池
const pool = mysql.createPool({
    host: process.env.DB_HOST,
    port: process.env.DB_PORT,
    user: process.env.DB_USERNAME,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_DATABASE,
    ssl: {
        minVersion: 'TLSv1.2',
        rejectUnauthorized: true
    },
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

// 初始化数据库（如果不存在则创建表）
async function initializeDatabase() {
    try {
        const createTableQuery = `
            CREATE TABLE IF NOT EXISTS restaurant (
                review_id INT AUTO_INCREMENT PRIMARY KEY,
                user_id VARCHAR(255) NOT NULL,
                user_name VARCHAR(255),
                restaurant_name VARCHAR(255) NOT NULL,
                restaurant_location VARCHAR(255) NOT NULL,
                dish_name VARCHAR(255),
                rating TINYINT NOT NULL,
                review_text TEXT NOT NULL
            )
        `;

        const createImageTableQuery = `
            CREATE TABLE IF NOT EXISTS review_images (
                id INT AUTO_INCREMENT PRIMARY KEY,
                review_id INT NOT NULL,
                image_url VARCHAR(2048) NOT NULL, 
                FOREIGN KEY (review_id) REFERENCES restaurant(review_id) ON DELETE CASCADE
            )
        `;
        
        await pool.query(createTableQuery);
        await pool.query(createImageTableQuery);
        console.log('数据库初始化成功');
    } catch (error) {
        console.error('数据库初始化失败:', error);
        throw error;
    }
}

// 导出连接池和初始化函数
module.exports = {
    pool,
    initializeDatabase
};
